SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

CREATE PROCEDURE [dbo].[usp_StatisticsRequireUpdate]
	(
      @param_JOL_Statistics_StartTime VARCHAR(50),
      @param_JOL_Statistics_EndTime VARCHAR(50)
	)
AS
SET @param_JOL_Statistics_StartTime = @param_JOL_Statistics_StartTime + '-01'
SET @param_JOL_Statistics_EndTime = @param_JOL_Statistics_EndTime + '-01'
DECLARE @Count int, @Fulltime int, @Internship int

WHILE @param_JOL_Statistics_StartTime <= @param_JOL_Statistics_EndTime
BEGIN
	SET @Fulltime =(select count(*)  
		from [dbo].[JOL_Require] as b 
		where year(b.JOL_Require_AddTime) = year(@param_JOL_Statistics_StartTime) 
			AND month(b.JOL_Require_AddTime) = month(@param_JOL_Statistics_StartTime)
			AND b.JOL_Require_Student_Type = 1
			AND JOL_Require_VerifyStatus = '已审核' )
	SET @Internship = (select count(*) 
		from [dbo].[JOL_Require] as b 
		where year(b.JOL_Require_AddTime) = year(@param_JOL_Statistics_StartTime) 
			AND month(b.JOL_Require_AddTime) = month(@param_JOL_Statistics_StartTime)
			AND b.JOL_Require_Student_Type = 2
			AND JOL_Require_VerifyStatus = '已审核')

	SET @Count = (SELECT COUNT(*) FROM dbo.JOL_Statistics_Require WHERE @param_JOL_Statistics_StartTime = Statistics_Date)
	IF (@Count = 0)
		begin
			INSERT INTO [dbo].[JOL_Statistics_Require]
			(
				Statistics_Date,
				Statistics_Fulltime,
				Statistics_Internship,
				Statistics_AddTime,
				Statistics_UpdateTime
			)
			values
			(
				@param_JOL_Statistics_StartTime,
				@Fulltime,
				@Internship,
				GETDATE(),
				GETDATE()
			)
		END
	ELSE
		BEGIN
			UPDATE [dbo].[JOL_Statistics_Require]
				SET Statistics_Fulltime = @Fulltime,
					Statistics_Internship = @Internship,
					Statistics_UpdateTime = GETDATE()
			WHERE Statistics_Date = @param_JOL_Statistics_StartTime
			
		END
	SET @param_JOL_Statistics_StartTime = CONVERT(VARCHAR(50), DATEADD(MONTH, 1, @param_JOL_Statistics_StartTime), 23)
END
GO
